One of the more difficult problems for BI reporting is to provide a pictorial representation of data. Graphs, tables or structural views are easy to provide from a tool such Reporting Services, but it requires more thought to provide images that give useful information when you are required to get data from a variety of different sources to be transformed, and then rendered into pictorial reports. In this article, I will be describing a way of providing a data-driven pictorial report in order to meet a particular business requirement. This requirement is to dynamically draw data-driven images and annotations onto a range of existing templates. I will take an actual use-case to illustrate the problem and show how it can be solved using SQL Server, Reporting Services and .NET.
Example use-case
In our example use-case, a ‘Vehicle Collision Repair’ business needs a software application that superimposes data about damaged vehicles onto existing 2D images of vehicle, using pre-defined templates from all makes and models of vehicle. The business must be able to View or Print a summary of any vehicle collision, and must be able to present this to their customers. This means that the software has to be able to create a printable view in PDF format that can be printed or shared by the user of the application.
Cost and performance
The business requires that
- The solution should not exhaust the available storage capacity.
- Pictorial reports should be flexible enough to be adaptable to any new source of imaging.
- Security should be an important factor while making and handling pictorial data.
- The solution must be built with technologies on hand, in this case with Microsoft stack .Net, SQL Server with BIT (Business Intelligence Tool).
Solution
The existing technological stack within the business would seem to provide everything necessary.
- Data is available in database tables to provide coordinates, annotation, UNC paths of vehicle images, and detail information about the repair process in the form of business data. Data storage is Microsoft SQL Server
- Microsoft SQL Server Reporting Service (SSRS) is the reporting engine capable of making reports in PDF format.
- .NET web framework.
Here is our ‘components view’.
SQL Database
We need data source from where data will be pulled to build our pictorial report. For this example, we will create one database with a few tables to store data and procedures to read data from tables, we will also insert few sample data to make part of our solution. First, we will create the database
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO CREATE DATABASE [CollisionDB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'CollisionDB', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'CollisionDB_log', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO |
We need to have one table Collision to store the customer information, the make of the vehicle, the date of recording the collision and the location path of vehicle template image. There is another table CollisionSite to store damage detail and location as site(s) with coordinate X and Y offsets, these coordinates help us to draw annotation or graphics or shapes on template image. To create these two tables here is the script with the foreign key.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
--Create table in CollisionDB USE [CollisionDB] GO CREATE TABLE [dbo].[Collision]( [CollisionId] [int] NOT NULL, [CustomerName] [varchar](50) NOT NULL, [Address] [varchar](150) NOT NULL, [VehicleMake] [varchar](50) NOT NULL, [DateOfRecord] [date] NOT NULL, [TemplateImagePath] [varchar](150) NULL, --location path of vehicle template image CONSTRAINT [PK_Collision] PRIMARY KEY CLUSTERED ( [CollisionId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[CollisionSite]( [CollisionId] [int] NOT NULL, [SiteNo] [int] NOT NULL, [SiteDetails] [varchar](250) NOT NULL, [LocationX] [int] NOT NULL, [LocationY] [int] NOT NULL, CONSTRAINT [PK_CollisionSites] PRIMARY KEY CLUSTERED ( [CollisionId] ASC, [SiteNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /*Foreign key*/ ALTER TABLE [dbo].[CollisionSite] WITH CHECK ADD CONSTRAINT [FK_CollisionSite_Collision] FOREIGN KEY([CollisionId]) REFERENCES [dbo].[Collision] ([CollisionId]) GO ALTER TABLE [dbo].[CollisionSite] CHECK CONSTRAINT [FK_CollisionSite_Collision] GO |
Here are the created tables and their foreign key constraints to keep data integrity. Collision table’s CollisionId is primary key and it is set as a foreign key on CollisionSite table’s CollisionId. Columns CollisionId and SiteNo is the composite primary key of CollisionSite.
These two tables will need data in order to demonstrate our solution, so we can use the next script to add collision information into the Collision table and two vehicle sites indicating damage information in CollisionSite table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO [dbo].[Collision] ([CollisionId],[CustomerName], [Address],[VehicleMake],[DateOfRecord],[TemplateImagePath]) SELECT 1 AS CollisionId,'Xyz, Abc Jr.' AS CustomerName, '000, Planet Earcth Country' AS [Address], 'Tunami Car 2020' AS [VehicalMake], '2014-01-01' AS [DateOfRecord], '\\VehicleBank\2020\Tunami.png' GO INSERT INTO [dbo].[CollisionSite](CollisionId,SiteNo,SiteDetails,LocationX,LocationY) SELECT 1 AS CollisionId,1 AS SiteNo,'Remove Dent',100 AS LocationX,100 AS LocationY UNION ALL SELECT 1 AS CollisionId,2 AS SiteNo,'Repaint',200 AS LocationX,100 AS LocationY GO |
Now that the Database is ready, we next need to have the procedures Report_GetCollision and Report_GetCollisionSites to fetch data for RDL report as well as the GetCollisionVehicleTemplateAndSitesById procedure for the HTTP RESTful interface to pull sites details with the help of ADO.Net. Here are the two stored procedures’ create scripts.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE PROCEDURE [dbo].[Report_GetCollision] AS BEGIN SET NOCOUNT ON; SELECT [CollisionId],[CustomerName],[Address] ,[VehicleMake],[DateOfRecord] FROM [dbo].[Collision]; GO CREATE PROCEDURE [dbo].[Report_GetCollisionSites] AS BEGIN SET NOCOUNT ON; SELECT SiteNo,SiteDetails FROM [dbo].[CollisionSite]; END GO CREATE PROCEDURE [dbo].[GetCollisionVehicleTemplateAndSitesById] @CollisionId INT AS BEGIN SET NOCOUNT ON; SELECT [TemplateImagePath] FROM [dbo].[Collision] WHERE [CollisionId] = @CollisionId; SELECT [SiteNo],[LocationX],[LocationY] FROM [dbo].[CollisionSite] WHERE [CollisionId] = @CollisionId END GO |
The Stored procedures Report_GetCollisionSites and Report_GetCollision pull all available rows from the table without a filter, because our tables only have, for illustration purposes, just one collision record. A third procedure GetCollisionVehicleTemplateAndSitesByIdwill be called with ADO.Net in a HTTP RESTful application. This logic we will have in CollisionManger.GetVehicleCollisionBy(collisionId). All these make our database ready with data as well as procedures to fetch the data for the need of RDL report and HTTP RESTful.
.Net HttpREST Application
There are numerous ways to create HTTP RESTful application and here are the few ways to build with .net framework.
- WCF service with wsHTTPbinding or webHTTPBinding(with .net 3.5 or .net 4.0 framework).
- ASP.Net MVC action method having file stream result.
- Custom written HTTP handler, for writing stream or bytes in HTTP response.
- ASP.Net MVC WebAPI.
Any option can help to build RESTful application, however .Net framework has ASP.Net, a strong web framework capable to create HTTP RESTful application as well; hence we’ll select ASP.Net WebAPI as one portion of our solution. The main steps to build the HTTP Restful application are to …
- Create a RESTful application with Asp.net MVC WebAPI, having a method taking HTTP GET verb and one parameter i.e. collisionId.
- Connect to DB for getting data to prepare the image with collision marks.
- Use “System.Drawing.dll” to dynamically draw on the template image.
- HTTP Response header with Cache-Control should have ” no cache” and ” no-store“.
- Return the image as HTTP response HttpResponseMessage and content type “image/png”.
- Windows authentication should be used to secure communication.
Moving on to the core part of the solution, We first created a WebAPI project. We added a new VehicleServiceController WebAPI controller class, added a GetVehicleImage public action method with the input parameter of Int and with an HttpResponseMessage return type. Finally, we added a PrepareCollisionImage private method to the class: The return type of this method is an array of bytes.
Here is the complete class having logic to read data from the business manager class CollisionManager, making use of the .NET drawing capability on the image and converting the final image stream to bytes for sending with the HTTP response.
C# Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
using System.Drawing; using System.IO; using System.Net; using System.Net.Http; using System.Net.Http.Headers; using System.Web.Http; using WebApi.CacheHelper; namespace WebApi.Controllers { public class VehicleServiceController : ApiController { /// /// Provides the collision image of the vehicle. /// /// Id of collision detail catured and recorded /// HTTP response haing image prepared with collision marks [NoCache] [HttpGet] [Route("GetVehicleImage/{collisionId}")] public HttpResponseMessage GetVehicleImage(int collisionId) { //Fetch vehicle collision detail in Vehicle instance, encapsulated call of ADO.Net to procedure [dbo].[GetCollisionVehicleTemplateAndSitesById], . Vehicle vehicle = CollisionManger.GetVehicleCollisionBy(collisionId); //Prepare HTTP response object, to send image to caller/client. HttpResponseMessage collisionImage = new HttpResponseMessage(HttpStatusCode.OK); //Prepare the image with collision collisionImage.Content = new ByteArrayContent(this.PrepareCollisionImage(vehicle)); //set the content type to make client undertsand what has been delivered. //e.g. "image/jpeg" or "image/png", here encapsulated in "vehicle.ImageType" collisionImage.Content.Headers.ContentType = new MediaTypeHeaderValue(vehicle.ImageType); return collisionImage; } /// <summary> /// Use graphics objects and build vehicle collision image /// </summary> /// <param id="vehicle"">Object of Vehicle</param> /// <returns></returns> private byte[] PrepareCollisionImage(Vehicle vehicle) { byte[] result = new byte[] { }; //Initialize Image object from file path and graphics object using (Image vehicleBody = Image.FromFile(vehicle.Image)) using (Graphics graphic = Graphics.FromImage(vehicleBody)) using (Brush ellipseBrush = new SolidBrush(Color.Blue)) using (Brush fontBrush = new SolidBrush(Color.Yellow)) using (MemoryStream inMemory = new MemoryStream()) { //Prepare graphics object foreach (var item in vehicle.Sites) { //Draw spot on vehicle body at specified X and Y coordinates graphic.FillEllipse(ellipseBrush, item.XOffsetValue, item.YOffsetValue, 40, 18); //Draw site index for easy identification graphic.DrawString(item.Index, new Font(FontFamily.GenericSansSerif, 9, FontStyle.Regular), fontBrush, item.XOffsetValue+15, item.YOffsetValue); } vehicleBody.Save(inMemory, System.Drawing.Imaging.ImageFormat.Bmp); inMemory.Seek(0, 0); result = inMemory.ToArray(); } return result; } } |
The method class VehicleServiceController.GetVehicleImage has [ NoCache] attribute added to eliminate caching of these images for security as well as for data-accuracy – to avoid stale images from cache. NoCache is the class inherited from ActionFilterAttribute, where the class has overridden OnActionExecuted by adding a cache control header to the http response object to prevent any caching of the action’s response context. Here is the complete code of NoCache class.
C# Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
using System.Net.Http.Headers; using System.Web.Http.Filters; namespace WebApi.CacheHelper { public class NoCache :ActionFilterAttribute { /// <summary> /// Method executed after target action execution is complete. /// </summary> /// <param id="actionExecutedContext""></param> public override void OnActionExecuted (HttpActionExecutedContext actionExecutedContext) { actionExecutedContext.Response.Headers.CacheControl = new CacheControlHeaderValue() { NoCache = true, NoStore = true }; base.OnActionExecuted(actionExecutedContext); } } } |
Let’s test this directly on the browser by providing complete URL, for an example “http://localhost/VehicleCollisionCenter/GetVehicleImage/1”. Here, I’ve added a screenshot of what the HTTP response headers look like for the image, with the help of browser’s network profiling from developer tools [Microsoft Internet Explorer].
Cache-Control has no – cache and no-store as values, and the content type is set to a PNG image. Now that we’re at the point that the dynamic image source is ready, let’s move on to the process that will consume this RESTful action i.e. SSRS RDL.
Note that n o-store has been added just to be sure that, even after no-cache, the image is not written to disk by the HTTP client.
SSRS RDL
We first add a new RDL to the reporting project. Since images cannot be built dynamically in SSRS with an RDL file, we will use our .NET HTTP RESTful application to serve dynamic images to the SSRS RDL, but the decision of what image is to be served will be made based on the parameter supplied by RDL. How? The dataset for the report will be provided by an SP or UDF from the database, let’s employ the column CollisionId from this dataset and the report parameter ImageBuilderURL to begin the process of getting the image into the RDL..Values from these two will help to build the URL which will be used by SSRS report execution process to get the external image. Once the image content arrives, SSRS will be able to render image in its report execution context with the help of the received HTTP response.
Let’s add one RDL [Report Definition Language] file to reporting project and start the making report. Add new data source to Shared Data Sources of report project; this should be SQL DB connection. Add the report parameter ‘ ImageBuilderURL‘ and two datasets for the added RDL file. After doing these actions, this is what we get in report project.
Datasets are embedded into the report: The shared data source that we have added is the source of data for the RDL, and we can add one of the types of procedure from the available option. In this use-case I have used stored procedures Report_GetCollisionSites and Report_GetCollision to build report’s dataset.
Now that the initial setting is done, we can design the RDL: For that, we will add some fields that are mapped to the dataset. We can simply drag and drop fields from the datasets. Customer, Address and date are generated by dragging on reports, for tabular representation of collision detail; we used the Table type of control, Text Box for field title and finally an Image control to show our dynamic images. This is the screen shot after all the controls are in place.
We need to do a few more things to the Image control to get the image from the external source. In the screen shot below, the fields marked with green and blue dots are the ones that are important for this process.
• “Select the Image source :” should be “External“.
•”Use this image :” should be “<<Expr>>“,
We can now write a VB [Visual Basic] function to make an expression. The logic of this function is to build an URL with the parameters collisionId and ImageBuilderURL. In this example we have the RESTFul action method hardcoded in order to partially confirm what the logic will return. From report properties add below “VehicleImage” function under “Code” section.
|
1 2 3 4 5 |
Public Function VehicleImage(ByVal collisionId As Integer, ByVal ImageBuilderURL As String) As String Dim RestURL As String RestURL = ImageBuilderURL + "/GetVehicleImage?collisionId=" + collisionId.tostring Return RestURL End Function |
Here is the screenshot that we get, now that the VB code has been added to it.
The function in custom code is a global function; it will be available in the expression window from now onwards. Click on the fx button of the “Use this image” field, to set the expression in the expression popup window. The expression is going to be ‘ =code.VehicleImage(Fields!CollisionId.Value,Parameters!ImageBuilderURL.Value)‘. 
At this point we have finished creating the RDL. Next, we need to make RESTful HTTP application and SSRS to communicate over the secured channel.
Secure Communication
In order to allow SSRS to successfully access an HTTP RESTful application in the same domain, the SSRS service should make an HTTP request with an identity account which does not need authorization on the remote server. Hence let’s use an account that is dedicated for SSRS: ‘ xyz\ssrExecutionAccount‘ has been set in Reporting Services Configuration Manager under the “Execution Account” section: Here is the picture of it.
Next. the HTTP RESTful application’s authentication is set to ‘ windows authentication‘ and has the authorization rule set to allow an incoming request with the identity of “xyz\ssrExecutionAccount” only. This is actually the same account as has been set for the SSRS Execution Account . That ensures a secured and seamless communication between the SSRS and the HTTP RESTful application. The authorization rule can be set from IIS or from the web.config of the HTTP RESTful application.
Example report
Here is an example of a summary report having the added vehicle-collision detail in it from the report designer. Two sites on the vehicle are marked on the image and some detail is appended at the bottom.
PDF Format
SSRS Reporting Service is capable of providing reports in many formats including PDF (Portable Document Format). The application that is displaying the PDF file should have a reference to the SSRS reporting web service added, this code snippet illustrates how to get the PDF from SSRS reporting service.
|
1 2 3 4 5 |
ReportExecutionService reportService = new ReportExecutionService(); reportService.Credentials = System.Net.CredentialCache.DefaultCredentials; //............ few code go here to prepare more parameters. byte[] pdfBytes = reportService .Render("PDF", null, out extension, out mimeType, out encoding, out w, out streamID); |
We can see that the SSRS reporting service proxy method ReportExecutionService.Render is able to provide PDF after supplying the content type “PDF” and relevant parameters; here the return type is an ‘array of byte’. This provides the flexibility that allows us to convert bytes to a file stream or writing directly to the file system. In the case of a web application, the byte array can be written to the HTTP response object in the form of a stream.
If the business requires a secure PDF that restricts content copy/print based on end-user privilege, then this can be done after receiving a byte array from Reporting Services. It is then possible to password-protect the PDF file with open source .net library like iTextSharp or any other PDF based .net library.
Conclusion
This solution meets the needs of the business because
- It requires no storage overhead because images with collision detail aren’t stored outside of the SQL Server database.
- It is flexible enough to allow template images of vehicles to be added or removed.
- Security can be built into the final output of RDL, allowing PDF content to be shared with the customer without needing the application.
- The superimposition of the dynamic Image can be made secure by building web security while fetching image from the HTTP Restful application.
Aid to business
Although I’ve used an accident-repair business as an example, a very similar requirement of being able to superimpose annotations, graphics and other images onto a range of existing templates can be found in other applications such as with Healthcare in recording injuries, or in investigations of crime. It is also particularly useful in annotating maps and there is a general use with reporting on data to do with physical entities in the real world.
Sometimes the skill in IT is not so much in developing entirely new and expensive techniques, but by adapting the technological stack that is already there in new ways to help businesses make the best use of their existing technological assets.
Load comments